SET NOCOUNT ON;
declare @release smallint = 10;
declare @version smallint = 0;
declare @script_name varchar(256);
declare @script_type varchar(20)='CensusData';
set @script_name=@script_type+'_V'+convert(varchar(5),@version)+'R'+right('00000'+convert(varchar(5),@release),5)+'.sql'

if exists (select 1 from dbo.database_version_information where script_name=@script_name and undone=0)
	print 'script '+@script_name+' already executed, please undo then re-execute.';
else
begin try
begin tran
/*--Begin Code Here --*/

if exists (select * from sys.views where object_id=object_id('dbo.Code_Plex_Table_Info'))
	drop view dbo.Code_Plex_Table_Info;

select
t.name as [table_name]
,c.name as [column_name]
,'|'+c.name+'|'
+ty.name
+case ty.system_type_id 
	when 167 then '('+convert(varchar(10),c.max_length) +')'
	when 175 then '('+convert(varchar(10),c.max_length) +')'
	else ''
end
+case c.is_nullable when 0 then ' not null ' else ' null ' end+'|'+replace(c.name,'_',' ')+'|' as [Wiki_Output]
,ty.system_type_id 
from
sys.tables t
inner join
sys.columns c
on
t.object_id=c.object_id
inner join
sys.types ty
on
ty.user_type_id =c.user_type_id 

RAISERROR('Code not complete, remove this line when finished.',16,1);
/*--End Code Here --*/
if exists (select 1 from dbo.database_version_information where script_name=@script_name and undone=1)
	update dbo.database_version_information 
	set undone=0
	,date_released=getdate()
	where
	script_name=@script_name;
else
	insert into dbo.database_version_information 
	values(@script_name,@script_type,@version,@release,GETDATE(),0);
	
print 'script '+@script_name+' succeeded!'
commit tran
end try
begin catch 
	declare @error_message varchar(2000);
	set @error_message='SCRIPT:'+@script_name+' FAILED! ROLLBACK!  ERROR_MESSAGE:'
	+coalesce(error_message(),'')+' ERROR_NUMBER:'+coalesce(convert(varchar(10),error_number()),'');
	ROLLBACK
	raiserror(@error_message,16,1)
end catch
